package model.dao;

import model.bean.User;

import java.sql.*;

public class Database {

    private String url = "jdbc:mysql://localhost:3306/lightQ?serverTimezone=UTC&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true";
    private static final String driver = "com.mysql.jdbc.Driver";
    private String username = "root";
    private String password = "trymybest";
    private Connection connection;
    private PreparedStatement preparedStatement;

    /**
     *连接数据库
     */
    public void connect() {
        try {
            Class.forName(driver).newInstance();
            connection = DriverManager.getConnection(url, username, password);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 实现数据库动态操作并返回结果集
     * 通过此方法可直接动态执行Sql语句
     * eg: execResult("select * from user where account = ?", account);
     * @param sql
     * @param data
     * @throws SQLException
     * @return
     */
    public ResultSet execResult(String sql, String... data) throws SQLException {
        preparedStatement = connection.prepareStatement(sql);
        for (int i = 1; i <= data.length; i++) {
            preparedStatement.setString(i, data[i -1]);
        }
        return preparedStatement.executeQuery();
    }

//    public ResultSet execResult(String sql, Object... data) throws SQLException {
//        preparedStatement = connection.prepareStatement(sql);
//        for (int i = 1; i <= data.length; i++) {
//            preparedStatement.setObject(i, data[i -1]);
//        }
//        return preparedStatement.executeQuery();
//    }

    /**
     * 实现动态更新信息（不返回数据）
     * eg: exec("insert into user values(?, ?)", "1", "2")
     * @param sql
     * @param data
     * @throws SQLException
     */
    public void exec(String sql, String... data) throws SQLException {
        preparedStatement = connection.prepareStatement(sql);
        for (int i=1; i<=data.length; i++) {
            preparedStatement.setString(i, data[i - 1]);
        }
        preparedStatement.executeUpdate();
    }

    //insert into user values (account, password, nickname, sex, birthday, phone, email, portrait)
    public void exec(User user) throws SQLException {
        String sql = "insert into user(account, password, nickname, sex, birthday, phone, email, portrait) values (?, ?, ?, ?, ?, ?, ?, ?)";
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.setString(1, user.getAccount());
        preparedStatement.setString(2, user.getPassword());
        preparedStatement.setString(3, user.getNickname());
        preparedStatement.setString(4, user.getSex());
        preparedStatement.setString(5, user.getBirthday());
        preparedStatement.setString(6, user.getPhone());
        preparedStatement.setString(7, user.getEmail());
        preparedStatement.setString(8, user.getPortrait());

        preparedStatement.executeUpdate();
    }

    /**
     * 实现数据库的静态操作
     * 只有sql语句一个参数，没有数据返回也没有数据传入
     * eg: exec("drop user")
     * @param sql
     * @throws SQLException
     */
    public void exec(String sql) throws SQLException{
        preparedStatement = connection.prepareStatement(sql);
        preparedStatement.executeUpdate();
    }

}
